package com.nmgydy.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.hibernate.Session;
import org.hibernate.SessionFactory;
import org.hibernate.jdbc.ReturningWork;

public class ProtoDao {

	/**
	 * 执行语句
	 * 
	 * @param cmdText
	 *            sql语句
	 * @return List<Map<String,String>> 键值列表
	 * @author fanyilun
	 * */
	public static List<Map<String, String>> executeQuery(SessionFactory sessionFactory, String cmdText, List<Object> cmdList) {
		final String sql = cmdText;
		final List<Object> sqlList = cmdList;
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		Session session = sessionFactory.openSession();

		try {
			ResultSet resultSet = session.doReturningWork(new ReturningWork<ResultSet>() {
				@Override
				public ResultSet execute(Connection connection) throws SQLException {
					return getResultSetBy(connection, sql, sqlList);
				}
			});

			ResultSetMetaData rsmd = resultSet.getMetaData();
			int count = rsmd.getColumnCount();

			int rows = 0; // 数据行数计数器
			while (resultSet.next()) {
				if (++rows > 1000000) {
					throw new Exception("查询数据量过大，请缩小查询范围！");
				}
				Map<String, String> map = new HashMap<String, String>();
				for (int i = 1; i <= count; i++) {
					map.put(rsmd.getColumnLabel(i), resultSet.getString(rsmd.getColumnLabel(i)));
				}
				list.add(map);
			}
		} catch (Exception e) {
			e.printStackTrace();
			return null;
		} finally {
			if (session!= null) {
				session.close();
			}
		}
		return list;
	}

	/**
	 * 执行分页查询
	 * 
	 * @param cmdText
	 *            sql语句
	 * @param pageNow
	 *            当前页
	 * @param pageSize
	 *            每页记录数
	 * @return List<Map<String,String>> 键值列表
	 * @author fanyilun
	 **/
	public static List<Map<String, String>> executeQuery(SessionFactory sessionFactory, String cmdText, int pageNow, int pageSize) {
		if (pageNow < 1)
			pageNow = 1;
		int start, rows;
		start = (pageNow - 1) * pageSize;
		rows = pageNow * pageSize - 1;

		// System.out.println(start +" - " +rows);

		final String sql = cmdText;
		// System.out.println(sql);
		List<Map<String, String>> list = new ArrayList<Map<String, String>>();
		Session session = sessionFactory.openSession();

		try {
			ResultSet resultSet = session.doReturningWork(new ReturningWork<ResultSet>() {
				@Override
				public ResultSet execute(Connection connection) throws SQLException {
					PreparedStatement preparedStatement = connection.prepareStatement(sql);
					ResultSet resultSet = preparedStatement.executeQuery();
					return resultSet;
				}
			});

			ResultSetMetaData rsmd = resultSet.getMetaData();
			int count = rsmd.getColumnCount();

			int it = -1;
			while (resultSet.next()) {
				it++;
				if (it < start)
					continue;
				if (it > rows)
					break;
				Map<String, String> map = new HashMap<String, String>();
				for (int i = 1; i <= count; i++) {
					map.put(rsmd.getColumnLabel(i), resultSet.getString(rsmd.getColumnLabel(i)));
				}
				list.add(map);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (session != null) {
				session.close();
			}
		}
		return list;
	}

	/**
	 * 返回首行数据
	 * 
	 * @param cmdText
	 *            sql语句
	 * @return Map<String,String> 键值
	 * @author fanyilun
	 * */
	public static Map<String, String> executeFirst(SessionFactory sessionFactory, String cmdText) {
		final String sql = cmdText;
		Map<String, String> map = new HashMap<String, String>();
		Session session = sessionFactory.openSession();

		try {
			ResultSet resultSet = session.doReturningWork(new ReturningWork<ResultSet>() {
				@Override
				public ResultSet execute(Connection connection) throws SQLException {
					PreparedStatement preparedStatement = connection.prepareStatement(sql);
					ResultSet resultSet = preparedStatement.executeQuery();
					return resultSet;
				}
			});

			ResultSetMetaData rsmd = resultSet.getMetaData();
			int count = rsmd.getColumnCount();

			if (resultSet.next()) {
				for (int i = 1; i <= count; i++) {
					map.put(rsmd.getColumnLabel(i), resultSet.getString(rsmd.getColumnLabel(i)));
				}
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			if (session != null) {
				session.close();
			}
		}

		return map;
	}

	/**
	 * 执行语句
	 * 
	 * @param cmdText
	 *            sql语句
	 * @return int 受影响条数
	 * @author fanyilun
	 * */
	public static int executeUpdate(SessionFactory sessionFactory, String cmdText) {
		final String sql = cmdText;
		Session session = null;
		int result = 0;
		try {
			session = sessionFactory.openSession();

			result = session.doReturningWork(new ReturningWork<Integer>() {
				@Override
				public Integer execute(Connection connection) throws SQLException {
					PreparedStatement preparedStatement = connection.prepareStatement(sql);
					preparedStatement.execute();
					int result = preparedStatement.getUpdateCount();
					return result;
				}
			});
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			if (session != null) {
				session.close();
			}
		}
		return result;
	}

	/**
	 * 获取记录集
	 * 
	 * @param connection
	 * @param sql
	 * @param list
	 * @return
	 * @throws SQLException
	 */
	public static ResultSet getResultSetBy(Connection connection, String sql, List<Object> sqlList) throws SQLException {
		PreparedStatement preparedStatement = connection.prepareStatement(sql);
		int i = 0;
		for (Object o : sqlList) {
			preparedStatement.setObject(++i, o);
		}
		ResultSet resultSet = preparedStatement.executeQuery();
		return resultSet;
	}

}
